﻿using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using OverStore.Sql.TableSchema;

namespace OverStore.Sql
{
    using Sc = System.StringComparer;
    using System.Text;

    /// <summary>
    /// Contains methods for building sql commands.
    /// </summary>
    public static class TableSqlBuilder
    {
        #region Table analyzing

        /// <summary>
        /// Return set of columns consists primary key for specified table schema.
        /// </summary>
        /// <param name="table">The <see cref="Table"/> for get key columns of it.</param>
        /// <returns>The set of primary keys of specified table schema.</returns>
        /// <exception cref="ArgumentNullException"><paramref name="table"/> is null reference.</exception>
        public static IEnumerable<KeyBase> KeyColumns(this Table table)
        {
            if (table == null)
                throw new ArgumentNullException("table");
            return table.Columns.OfType<KeyBase>().Where(c => c.ColumnType == ColumnType.PrimaryKey);
        }

        /// <summary>
        /// Return columns that are not part of primary key for specified table schema.
        /// </summary>
        /// <param name="table">The table for get non-key columns of it.</param>
        /// <returns>The set of non-primary key columns of specified table schema.</returns>
        /// <exception cref="ArgumentNullException"><paramref name="table"/> is null reference.</exception>
        public static IEnumerable<ColumnBase> NonKeyColumns(this Table table)
        {
            if (table == null)
                throw new ArgumentNullException("table");
            return table.Columns.Where(c => c.ColumnType == ColumnType.Regular);
        }

        /// <summary>
        /// Return primary keys and version columns.
        /// </summary>
        /// <param name="table">The table to get specified columns.</param>
        /// <returns>The enumeration contains key columns and row version column.</returns>
        /// <exception cref="ArgumentNullException"><paramref name="table"/> is null reference.</exception>
        public static IEnumerable<ColumnBase> KeyAndVersionColumns(this Table table)
        {
            if (table == null)
                throw new ArgumentNullException("table");
            return table.Columns.Where(c => c.ColumnType == ColumnType.PrimaryKey || c.ColumnType == ColumnType.RowVersion);
        }

        /// <summary>
        /// Return type discriminator columns for specified table schema.
        /// </summary>
        /// <param name="table">The table to get specified columns from.</param>
        /// <returns>Set of columns uses as type discriminator.</returns>
        /// <exception cref="ArgumentNullException"><paramref name="table"/> is null reference.</exception>
        public static IEnumerable<ColumnBase> TypeDiscriminatorColumns(this Table table)
        {
            if (table == null)
                throw new ArgumentNullException("table");
            return table.Columns.Where(c => StringComparer.OrdinalIgnoreCase.Equals(c.Name, table.TypeDiscriminatorColumnName ?? String.Empty));
        }

        #endregion

        #region Sql Commands building

        /// <summary>
        /// Create SQL statement for select all columns for single row specified by
        /// primary keys.
        /// </summary>
        /// <param name="tableSchema">The table schema.</param>
        /// <param name="options">The formatting options.</param>
        /// <returns>SQL statement selects single row using primary keys as filter from specified table.</returns>
        /// <exception cref="ArgumentNullException"><paramref name="table"/> is null reference.</exception>
        /// <exception cref="ArgumentNullException"><paramref name="options"/> is null reference.</exception>
        /// <exception cref="ArgumentException"><see cref="Table.Name"/> of <paramref name="table"/> instance is null reference or empty string.</exception>
        public static string CreateSelectSingleStatement(this Table tableSchema, SqlFormatting options)
        {
            if (tableSchema == null)
                throw new ArgumentNullException("tableSchema");
            if (options == null)
                throw new ArgumentNullException("options");
            if (String.IsNullOrEmpty(tableSchema.Name))
                throw new ArgumentException("Table Name is missing.");

            var buffer = new StringBuilder();
            buffer.Append("SELECT ");
            tableSchema.Columns.JoinStringsToBuffer(buffer, ", ", c => tableSchema.FormatSelectWithAlias(c, options));
            buffer.Append(" FROM ");
            buffer.Append(tableSchema.FormatName(options));
            buffer.Append(" WHERE ");
            tableSchema.KeyColumns().JoinStringsToBuffer(buffer, " AND ", c => tableSchema.FormatComparsionWithOriginalIdentifier(c, options));

            return buffer.ToString();
        }

        /// <summary>
        /// Create SQL statement for select all records from table.
        /// </summary>
        /// <param name="tableSchema">The table schema.</param>
        /// <param name="options">The sql formatting options.</param>
        /// <returns>SQL statement selects all rows from specified table.</returns>
        /// <exception cref="ArgumentNullException"><paramref name="table"/> is null reference.</exception>
        /// <exception cref="ArgumentNullException"><paramref name="options"/> is null reference.</exception>
        /// <exception cref="ArgumentException"><see cref="Table.Name"/> of <paramref name="table"/> instance is null reference or empty string.</exception>
        public static string CreateSelectAllStatement(this Table tableSchema, SqlFormatting options)
        {
            if (tableSchema == null)
                throw new ArgumentNullException("tableSchema");
            if (options == null)
                throw new ArgumentNullException("options");
            if (String.IsNullOrEmpty(tableSchema.Name))
                throw new ArgumentException("Specified Table has missing name.", "tableSchema");

            var buffer = new StringBuilder();
            buffer.Append("SELECT ");
            tableSchema.Columns.JoinStringsToBuffer(buffer, ", ", c => tableSchema.FormatSelectWithAlias(c, options));
            buffer.Append(" FROM ");
            buffer.Append(tableSchema.FormatName(options));

            return buffer.ToString();
        }

        /// <summary>
        /// Create SQL statement for select all primary keys and type discriminators from specified table.
        /// </summary>
        /// <param name="tableSchema">The table schema.</param>
        /// <param name="options">The options.</param>
        /// <returns>SQL statement selects all primary keys specified table.</returns>
        /// <exception cref="ArgumentNullException"><paramref name="table"/> is null reference.</exception>
        /// <exception cref="ArgumentNullException"><paramref name="options"/> is null reference.</exception>
        /// <exception cref="ArgumentException"><see cref="Table.Name"/> of <paramref name="table"/> instance is null reference or empty string.</exception>
        public static string CreateSelectAllKeysStatement(this Table tableSchema, SqlFormatting options)
        {
            if (tableSchema == null)
                throw new ArgumentNullException("tableSchema");
            if (options == null)
                throw new ArgumentNullException("options");
            if (String.IsNullOrEmpty(tableSchema.Name))
                throw new ArgumentException("Specified Table has missing name.", "tableSchema");

            var buffer = new StringBuilder();
            buffer.Append("SELECT ");
            tableSchema.KeyColumns()
                       .Cast<ColumnBase>()
                       .Concat(tableSchema.TypeDiscriminatorColumns())
                       .JoinStringsToBuffer(buffer, ", ", c => tableSchema.FormatSelectWithAlias(c, options));
            buffer.Append(" FROM ");
            buffer.Append(tableSchema.FormatName(options));

            return buffer.ToString();
        }

        /// <summary>
        /// Create SQL statement for update single row.
        /// </summary>
        /// <param name="tableSchema">The table schema.</param>
        /// <param name="options">The options.</param>
        /// <returns>
        /// SQL statement updates single row of specified table using primary keys as filter .
        /// </returns>
        /// <exception cref="ArgumentNullException"><paramref name="table"/> is null reference.</exception>
        /// <exception cref="ArgumentNullException"><paramref name="options"/> is null reference.</exception>
        /// <exception cref="ArgumentException"><see cref="Table.Name"/> of <paramref name="table"/> instance is null reference or empty string.</exception>
        public static string CreateUpdateStatement(this Table tableSchema, SqlFormatting options)
        {
            if (tableSchema == null)
                throw new ArgumentNullException("tableSchema");
            if (options == null)
                throw new ArgumentNullException("options");
            if (String.IsNullOrEmpty(tableSchema.Name))
                throw new ArgumentException("Table Name is missing");

            var buffer = new StringBuilder();
            buffer.Append("UPDATE ");
            buffer.Append(tableSchema.FormatName(options));
            buffer.Append(" SET ");
            tableSchema.Columns
                       .Where(c => !c.SkipOnUpdate)
                       .Where(c => c.ColumnType != ColumnType.PrimaryKey || (c as KeyBase).IsMutable)
                       .JoinStringsToBuffer(buffer, ", ", c => tableSchema.FormatComparsionWithParameter(c, options));
            buffer.Append(" WHERE ");
            tableSchema.KeyAndVersionColumns()
                       .JoinStringsToBuffer(buffer, " AND ", c => tableSchema.FormatComparsionWithOriginalIdentifier(c, options));

            return buffer.ToString();
        }

        /// <summary>
        /// 	<para>
        /// Create SQL statement for insert single row.
        /// </para>
        /// </summary>
        /// <param name="tableSchema">The table schema.</param>
        /// <param name="options">The options.</param>
        /// <returns>
        /// SQL statement inserts single row into specified table.
        /// </returns>
        /// <exception cref="ArgumentNullException"><paramref name="tableSchema"/> is null reference.</exception>
        /// <exception cref="ArgumentNullException"><paramref name="options"/> is null reference.</exception>
        /// <exception cref="ArgumentException"><see cref="Table.Name"/> of <paramref name="table"/> instance is null reference or empty string.</exception>
        public static string CreateInsertStatement(this Table tableSchema, SqlFormatting options)
        {
            if (tableSchema == null)
                throw new ArgumentNullException("tableSchema");
            if (options == null)
                throw new ArgumentNullException("options");
            if (String.IsNullOrEmpty(tableSchema.Name))
                throw new ArgumentException("Table name is missing");

            var buffer = new StringBuilder();
            buffer.Append("INSERT INTO ");
            buffer.Append(tableSchema.FormatName(options));
            buffer.Append(" (");
            tableSchema.Columns
                       .Where(c => !c.SkipOnInsert)
                       .JoinStringsToBuffer(buffer, ", ", c => options.Column(c.Name));
            buffer.Append(") VALUES ( ");
            tableSchema.Columns
                       .Where(c => !c.SkipOnInsert)
                       .JoinStringsToBuffer(buffer, ", ", c => c.ToResultsetNameOrOriginalName(options));
            buffer.Append(" )");

            return buffer.ToString();
        }

        /// <summary>
        /// Creates the SQL instruction that selects last result generated after inserting into column with identity.
        /// </summary>
        /// <param name="tableSchema">The table schema.</param>
        /// <param name="options">The options.</param>
        public static string CreateSelectLastIdentity(this Table tableSchema, SqlFormatting options)
        {
            if (Object.ReferenceEquals(tableSchema, null))
                throw new ArgumentNullException("tableSchema");
            if (Object.ReferenceEquals(options, null))
                throw new ArgumentNullException("options");
            if (String.IsNullOrEmpty(tableSchema.Name))
                throw new ArgumentException("Table Name is missing.");
            var key = tableSchema.KeyColumns().FirstOrDefault();
            if (key == null)
                throw new ArgumentException("Table does not contain key columns.");
            return options.LastIdentifierOfTable(key.IdentifierGeneratorName ?? tableSchema.Name, key.NewIdentifierName);
        }

        /// <summary>
        /// Create SQL statement for delete single row.
        /// </summary>
        /// <param name="tableSchema">The table schema.</param>
        /// <param name="options">The options.</param>
        /// <returns>SQL statement deletes single row using primary keys as filter from specified table.</returns>
        /// <exception cref="ArgumentNullException"><paramref name="table"/> is null reference.</exception>
        /// <exception cref="ArgumentNullException"><paramref name="options"/> is null reference.</exception>
        /// <exception cref="ArgumentException"><see cref="Table.Name"/> of <paramref name="table"/> instance is null reference or empty string.</exception>
        public static string CreateDeleteStatement(this Table tableSchema, SqlFormatting options)
        {
            if (tableSchema == null)
                throw new ArgumentNullException("tableSchema");
            if (options == null)
                throw new ArgumentNullException("options");
            if (String.IsNullOrEmpty(tableSchema.Name))
                throw new ArgumentException("Table name is missing");
            var buffer = new StringBuilder();
            buffer.Append("DELETE FROM ");
            buffer.Append(tableSchema.FormatName(options));
            buffer.Append(" WHERE ");
            tableSchema.KeyAndVersionColumns()
                       .JoinStringsToBuffer(buffer, " AND ", c => tableSchema.FormatComparsionWithOriginalIdentifier(c, options));

            return buffer.ToString();
        }

        /// <summary>
        /// Create SQL statement for select single row from set of tables in case
        /// when each table used for store data for single class.
        /// Each table should have single primary key.
        /// Primary key values should be unique over all tables included in query but they may have different names.
        /// Columns of different tables that has same names should have same or compatible types.
        /// Discriminator field name should be unique over all column names of all tables.
        /// Primay key parameter name should be unique over all column names of all table and should differs from
        /// discriminator field name.
        /// Each table should have filled <see cref="Table.TypeId"/> result, and this result should be unique over tables.
        /// </summary>
        /// <param name="tables">The set of tables consists inheritance hierarchy.</param>
        /// <param name="discriminatorFieldName">Name of the discriminator field.</param>
        /// <param name="primaryKeyParameterName">Name of the primary key parameter.</param>
        /// <param name="options">The options.</param>
        /// <returns>The result SQL command.</returns>
        /// <remarks>
        /// Create sql statement that allows select one row of from set of tables by single query.
        /// Usually used for implement inheritance hierarchy storaging where each non-abstract class
        /// stored in separated table.
        /// Method returns SQL contains union of select single row statements for each table.
        /// Resulted SQL command returns single row if primary key result is unique over table set.
        /// </remarks>
        /// <exception cref="ArgumentNullException"><paramref name="tables"/> is null reference.</exception>
        /// <exception cref="ArgumentNullException"><paramref name="discriminatorFieldName"/> is null reference or empty string.</exception>
        /// <exception cref="ArgumentNullException"><paramref name="primaryKeyParameterName"/> is null reference or empty string.</exception>
        /// <exception cref="ArgumentNullException"><paramref name="options"/> is null reference.</exception>
        /// <exception cref="ArgumentException"><see cref="Table.Name"/> of <paramref name="table"/> instance is null reference or empty string.</exception>
        /// <exception cref="ArgumentNullException"><paramref name="options"/> is null reference.</exception>
        public static string CreateTablePerClassInheritanceSelectSingle(
                             this IEnumerable<Table> tables,
                             string discriminatorFieldName,
                             string primaryKeyParameterName,
                             SqlFormatting options)
        {
            if (tables == null)
                throw new ArgumentNullException("tables");
            if (String.IsNullOrEmpty(discriminatorFieldName))
                throw new ArgumentNullException("discriminatorFieldName");
            if (String.IsNullOrEmpty(primaryKeyParameterName))
                throw new ArgumentNullException("primaryKeyParameterName");
            if (options == null)
                throw new ArgumentNullException("options");

            discriminatorFieldName = discriminatorFieldName.Trim();
            primaryKeyParameterName = primaryKeyParameterName.Trim();
            if (Sc.OrdinalIgnoreCase.Equals(discriminatorFieldName, primaryKeyParameterName))
                throw new ArgumentException("Primary key parameter name is equal to discriminator field name.");

            ThrowIfTableStructureInvalid(tables, discriminatorFieldName, primaryKeyParameterName);

            // Now when all agruments cheked, start sql generation.
            // Sql command generation has very simple principle - 
            // union of commands each select one row from table where primary key equals to primaryKeyParameterName
            // Because each table may have different set of columns, let normalize column set by adding 
            // missing columns with null values.
            // Also adds first column contains discriminator result.
            var columnsSet = tables.SelectMany(t => t.Columns).Select(c => c.Name).Distinct(Sc.OrdinalIgnoreCase);
            Func<Table, string> columnsStringFromTable = (t) => new[] 
                                                                { 
                                                                   String.Format(CultureInfo.InvariantCulture, "'{0}' AS {1}", t.TypeId, options.Column(discriminatorFieldName)) 
                                                                }

                                                                .Concat(columnsSet.Select(_c => t.Columns.Any(__tc => Sc.OrdinalIgnoreCase.Equals(__tc.Name, _c))
                                                                                                 ? options.Column(_c)
                                                                                                 : "NULL AS " + options.Column(_c)))
                                                                .JoinToString(", ", __item => __item);

            Func<Table, string> whereStringFromTable = (t) => String.Format(
                                                                     CultureInfo.InvariantCulture,
                                                                     "{0} = {1}",
                                                                     options.Column(t.KeyColumns().First().Name),
                                                                     options.Parameter(primaryKeyParameterName));

            var selectSingleRow = tables.Select(t => String.Format(
                                                                CultureInfo.InvariantCulture,
                                                                "SELECT {0} FROM {1} WHERE {2}",
                                                                columnsStringFromTable(t),
                                                                options.Table(null, t.Name),
                                                                whereStringFromTable(t)));
            return selectSingleRow.JoinToString("\r\n\tUNION\r\n", s => s);
        }

        /// <summary>
        /// Checks structure of all specified tables to contains primary keys, type discriminators etc.
        /// </summary>
        private static void ThrowIfTableStructureInvalid(IEnumerable<Table> tables, string discriminatorFieldName, string primaryKeyParameterName)
        {
            if (tables.SelectMany(t => t.Columns).Select(c => c.Name).Any(n => Sc.OrdinalIgnoreCase.Equals(n.Trim(), primaryKeyParameterName)))
                throw new ArgumentException("Primary key name match column name of specified table.");
            if (tables.SelectMany(t => t.Columns).Select(c => c.Name).Any(n => Sc.OrdinalIgnoreCase.Equals(n.Trim(), discriminatorFieldName)))
                throw new ArgumentException("Primary key name match column name of specified table.");
            if (tables.Any(t => String.IsNullOrEmpty(t.TypeId)))
                throw new ArgumentException("TypeId is missing for table.");
            if (tables.Select(t => t.TypeId).Distinct(StringComparer.OrdinalIgnoreCase).Count() != tables.Count())
                throw new ArgumentException("TypeId values is not unique.");
            if (tables.Any(t => t.KeyColumns().Count() != 1))
                throw new ArgumentException("There is table with missing primary key or composite primary key.");
        }

        #endregion

        #region Formatting methods

        /// <summary>
        /// Enclose name of table or column with symbols used for 
        /// escape names in current formatting options.
        /// </summary>
        private static string FormatName(this Table tableSchema, SqlFormatting options)
        {
            if (tableSchema == null)
                throw new ArgumentNullException("tableSchema");
            if (options == null)
                throw new ArgumentNullException("options");
            if (String.IsNullOrEmpty(tableSchema.Name))
                throw new ArgumentException("Invalid table name");

            return options.Table(null, tableSchema.Name);
        }

        /// <summary>
        /// Return escaped name of column prefixed with escaped name of table.
        /// </summary>
        private static string FormatColumn(this Table tableSchema, ColumnBase column, SqlFormatting options)
        {
            if (tableSchema == null)
                throw new ArgumentNullException("tableSchema");
            if (column == null)
                throw new ArgumentNullException("column");
            if (options == null)
                throw new ArgumentNullException("options");
            if (String.IsNullOrEmpty(tableSchema.Name))
                throw new ArgumentException("Table Name is missing");
            if (String.IsNullOrEmpty(column.Name))
                throw new ArgumentException("Column Name is missing");

            return options.Column(options.Table(null, tableSchema.Name), column.Name);
        }

        /// <summary>
        /// Convert parameter name into string that supports by specified ADO.NET provider.
        /// </summary>
        private static string FormatParameter(this ColumnBase column, SqlFormatting options)
        {
            if (column == null)
                throw new ArgumentNullException("column");
            if (options == null)
                throw new ArgumentNullException("options");
            return options.Parameter(column.ResultSetName);
        }

        /// <summary>
        /// Return string contains equality comparsion of specified column with specified parameter.
        /// </summary>
        private static string FormatComparsionWithParameter(this Table table, ColumnBase column, SqlFormatting options)
        {
            if (table == null)
                throw new ArgumentNullException("table");
            if (column == null)
                throw new ArgumentNullException("column");
            if (options == null)
                throw new ArgumentNullException("options");
            return String.Format(CultureInfo.InvariantCulture, "{0} = {1}", table.FormatColumn(column, options), column.FormatParameter(options));
        }

        /// <summary>
        /// Formats the comparsion key column with parameter contains original immutable identifier part.
        /// </summary>
        private static string FormatComparsionWithOriginalIdentifier(this Table table, ColumnBase column, SqlFormatting options)
        {
            if (table == null)
                throw new ArgumentNullException("table");
            if (column == null)
                throw new ArgumentNullException("column");
            if (options == null)
                throw new ArgumentNullException("options");
            var originalIdName = column.ResultSetName;
            var k = column as KeyBase;
            if (k != null)
                originalIdName = k.OriginalIdentifierName;
            return String.Format(CultureInfo.InvariantCulture, "{0} = {1}", table.FormatColumn(column, options), options.Parameter(originalIdName));
        }

        /// <summary>
        /// Formats the part of SELECT statement consists of escaped full qualified column name with assigned alias.
        /// </summary>
        private static string FormatSelectWithAlias(this Table table, ColumnBase column, SqlFormatting options)
        {
            if (table == null)
                throw new ArgumentNullException("table");
            if (column == null)
                throw new ArgumentNullException("column");
            if (options == null)
                throw new ArgumentNullException("options");
            return String.Format(CultureInfo.InvariantCulture, "{0} AS {1}", table.FormatColumn(column, options), options.Column(column.ResultSetName));
        }

        /// <summary>
        /// If column is key column format OriginalIdentifierName as parameter, otherwise format ResultSetName as parameter.
        /// </summary>
        private static string ToResultsetNameOrOriginalName(this ColumnBase column, SqlFormatting options)
        {
            if (column == null)
                throw new ArgumentNullException("column");
            if (options == null)
                throw new ArgumentNullException("options");
            var kk = column as KeyBase;
            if (kk != null)
                return kk.IsMutable ? options.Parameter(kk.ResultSetName) : options.Parameter(kk.OriginalIdentifierName);
            else
                return options.Parameter(column.ResultSetName);
        }

        #endregion

        /// <summary>
        /// Join sequence of objects into single string using specified separator and 
        /// function that converts each object into its string representation.
        /// </summary>
        private static string JoinToString<T>(this IEnumerable<T> source, string separator, Func<T, string> stringConverter)
        {
            if (source == null)
                throw new ArgumentNullException("source");
            if (String.IsNullOrEmpty(separator))
                throw new ArgumentNullException("separator");
            if (stringConverter == null)
                throw new ArgumentNullException("stringConverter");
            var buffer = new StringBuilder();
            var en = source.GetEnumerator();
            if (en.MoveNext())
            {
                buffer.Append(stringConverter(en.Current));
            }

            while (en.MoveNext())
            {
                buffer.Append(separator);
                buffer.Append(stringConverter(en.Current));
            }

            return buffer.ToString();
        }

        /// <summary>
        /// Join sequence of objects into single string using specified separator and 
        /// function that converts each object into its string representation.
        /// </summary>
        private static void JoinStringsToBuffer<T>(this IEnumerable<T> source, StringBuilder buffer, string separator, Func<T, string> stringConverter)
        {
            if (source == null)
                throw new ArgumentNullException("source");
            if (String.IsNullOrEmpty(separator))
                throw new ArgumentNullException("separator");
            if (stringConverter == null)
                throw new ArgumentNullException("stringConverter");
            var en = source.GetEnumerator();
            if (en.MoveNext())
            {
                buffer.Append(stringConverter(en.Current));
            }

            while (en.MoveNext())
            {
                buffer.Append(separator);
                buffer.Append(stringConverter(en.Current));
            }
        }
    }
}